Using XLOOKUP for Flexible Data Searching in Excel

Course: Advanced Excel Training

Topic: Using XLOOKUP for Flexible Data Searching

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to XLOOKUP

XLOOKUP is a powerful lookup function in Microsoft Excel used to search for a specific value in a range or array and return a corresponding value from another range. It makes searching, retrieving, and analyzing spreadsheet data easier and more flexible.

For example, a company may maintain an employee database containing employee IDs, names, departments, salaries, and joining dates. Instead of manually searching through hundreds of rows, XLOOKUP can retrieve an employee's details by entering their employee ID.

XLOOKUP is useful in employee management, inventory tracking, sales reports, financial analysis, customer databases, product pricing, and automated dashboards. It can search vertically or horizontally, return results from either side of the lookup column, support approximate matches, and handle missing values with a custom message.

Unlike traditional VLOOKUP, XLOOKUP does not require a column index number and defaults to an exact match. These features make it easier to maintain formulas when worksheets change.

2. Learning Objectives

• Understand the purpose and syntax of the XLOOKUP function.
• Search for values in rows, columns, and structured datasets.
• Retrieve matching information from another range.
• Use custom messages when lookup values are missing.
• Perform exact, approximate, and wildcard searches.
• Search from the beginning or end of a data range.
• Return multiple columns from a single lookup formula.
• Combine XLOOKUP with IFERROR, SUM, and other Excel functions.
• Compare XLOOKUP with VLOOKUP, HLOOKUP, and INDEX-MATCH.
• Build practical lookup models for business reporting.

3. Syntax of the XLOOKUP Function

The general syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The first three arguments are sufficient for most basic lookup tasks. The remaining arguments provide additional control over missing values, approximate matches, wildcards, and search direction.

4. Understanding the XLOOKUP Arguments

4.1 Lookup Value

The lookup value is the item you want Excel to find. It can be a cell reference, text value, number, or calculated expression.

Examples include employee IDs, product codes, customer names, invoice numbers, and order IDs.

=XLOOKUP(E2,A2:A10,B2:B10)

In this example, Excel searches for the value stored in E2.

4.2 Lookup Array

The lookup array is the range in which Excel searches for the lookup value. It should contain the values you want to match.

For example, A2:A10 might contain employee IDs.

4.3 Return Array

The return array contains the corresponding values Excel should return after finding a match.

For example, B2:B10 might contain employee names corresponding to the IDs in A2:A10.

4.4 If Not Found

This optional argument lets you display a meaningful message instead of the #N/A error when a match is not found.

=XLOOKUP(E2,A2:A10,B2:B10,"Employee Not Found")

4.5 Match Mode

The match_mode argument controls how Excel matches the lookup value.

4.6 Search Mode

Important: Binary search modes require correctly sorted lookup data. Using them on unsorted data can produce incorrect results.

5. Basic Example: Searching for an Employee Name

Suppose a worksheet contains the following employee data.

Assume employee IDs are in A2:A5 and employee names are in B2:B5. Enter the employee ID you want to find in E2.

Use this formula to retrieve the employee name:

=XLOOKUP(E2,A2:A5,B2:B5,"Employee Not Found")

If E2 contains E103, Excel returns Rahul Verma.

How it works:

1. Excel reads the lookup value in E2.
2. It searches for the ID in A2:A5.
3. When a match is found, Excel identifies the corresponding row.
4. It returns the employee name from the same row in B2:B5.
5. If the ID is missing, the formula returns "Employee Not Found".

6. Searching for Data in Another Column

XLOOKUP can return information from a column located either to the left or right of the lookup column.

For example, suppose employee names are in column B and employee IDs are in column A. If you want to search by employee name and return the employee ID, use:

=XLOOKUP(E2,B2:B5,A2:A5,"Employee Not Found")

If E2 contains Neha Patel, the formula returns E102.

This flexibility removes the left-to-right restriction associated with traditional VLOOKUP.

7. Retrieving Employee Salaries

Suppose a company maintains an employee salary database.

Assume employee IDs are in A2:A5, salaries are in D2:D5, and the required ID is entered in F2.

=XLOOKUP(F2,A2:A5,D2:D5,"Salary Not Available")

If F2 contains E103, the formula returns ₹50,000.

This formula can be used in HR reports, payroll worksheets, and employee information dashboards. Access to salary information should be limited to authorized users.

8. Returning Multiple Columns with One XLOOKUP Formula

XLOOKUP can return multiple adjacent columns from the matching row when the return array includes more than one column. In Excel versions that support dynamic arrays, the results can spill into neighboring cells automatically.

Suppose the employee database contains IDs in A2:A5, names in B2:B5, and departments in C2:C5.

=XLOOKUP(E2,A2:A5,B2:C5,"Employee Not Found")

If E2 contains E102, Excel returns Neha Patel and Finance in two adjacent cells.

Make sure the cells to the right of the formula are empty so that the returned values can spill. Otherwise, Excel may display a #SPILL! error.

9. Using XLOOKUP for Product Price Searching

Product catalogs often contain product codes, names, prices, and stock quantities. XLOOKUP can retrieve the correct information from the product code.

Assume product codes are in A2:A5, product names in B2:B5, and prices in C2:C5. The selected product code is entered in E2.

To retrieve the product name:

=XLOOKUP(E2,A2:A5,B2:B5,"Product Not Found")

To retrieve the price:

=XLOOKUP(E2,A2:A5,C2:C5,"Price Not Available")

If E2 contains P103, the price formula returns ₹8,500.

This method is useful for quotation sheets, inventory reports, order forms, and automated invoices.

10. Using XLOOKUP with Approximate Matching

Approximate matching is useful when a value falls between defined thresholds, such as commission rates, discount bands, grading scales, or shipping charges.

Suppose a commission table contains minimum sales thresholds.

Assume the thresholds are in E2:E5, commission rates are in F2:F5, and the sales value is in B2. Use:

=XLOOKUP(B2,E2:E5,F2:F5,"No Rate",-1)

The match mode -1 returns an exact match or the next smaller threshold. For sales of ₹60,000, Excel returns 6%.

To calculate the commission amount directly:

=B2*XLOOKUP(B2,E2:E5,F2:F5,"No Rate",-1)

Ensure the threshold table is arranged in ascending order for clarity and easier maintenance. This example assumes the first threshold is 0 and sales cannot be negative.

11. Using XLOOKUP with Wildcard Matching

Wildcard matching is helpful when the exact text is unknown or only part of a name, code, or description is available.

The wildcard match mode is 2.

• * matches any number of characters, including no characters.
• ? matches exactly one character.
• ~ is used to search for a literal asterisk or question mark.

Example: Searching for a Partial Product Name

Suppose product names are stored in B2:B10 and prices in C2:C10. Cell E2 contains a partial product name.

=XLOOKUP("*"&E2&"*",B2:B10,C2:C10,"Product Not Found",2)

If E2 contains "Mouse", the formula searches for a product name containing that text and returns the price of the first matching record.

For example, a search for "Mouse" could match "Wireless Mouse" if that item exists in the lookup range.

Note: Wildcard searches generally return the first matching result. If several product names contain the same text, make sure the first match is appropriate or refine the search term.

12. Searching from the Last Record

By default, XLOOKUP searches from the beginning of the lookup array. The search_mode argument can change the direction of the search.

Use -1 to search from the last item to the first.

Example: Finding the Most Recent Price Record

Suppose a transaction log contains repeated product codes because prices have changed over time.

If product codes are in A2:A5, prices are in C2:C5, and the requested code is in E2, use:

=XLOOKUP(E2,A2:A5,C2:C5,"Product Not Found",0,-1)

If E2 contains P101, the formula returns ₹850 because it finds the last occurrence of P101 in the list.

This technique is useful for transaction logs and append-only records where the most recent entry is at the bottom. The last record is not necessarily the newest by date if records are not maintained in chronological order.

13. Using XLOOKUP Horizontally

XLOOKUP can search across a row as well as down a column. This makes it useful for retrieving information from monthly reports, financial summaries, and horizontal data tables.

Suppose the months are stored in B1:E1 and the sales figures are in B2:E2.

If G1 contains the month you want to find, use:

=XLOOKUP(G1,B1:E1,B2:E2,"Month Not Found")

If G1 contains March, the formula returns 72,000.

This makes XLOOKUP a flexible alternative to HLOOKUP for horizontal searches.

14. Using XLOOKUP with Multiple Criteria

Sometimes a single value is not sufficient to identify the correct record. For example, a sales database may contain repeated product codes for different regions. A lookup may need to match both the product code and the region.

Assume product codes are in A2:A10, regions are in B2:B10, and sales values are in C2:C10. The product code is in E2 and the region is in F2.

=XLOOKUP(1,(A2:A10=E2)*(B2:B10=F2),C2:C10,"No Matching Record")

The formula creates two logical arrays. Multiplying them causes a row to evaluate to 1 only when both conditions are TRUE. XLOOKUP then returns the sales value from the matching row.

For example, if E2 contains P101 and F2 contains West, the formula returns the sales value for product P101 in the West region.

This approach is useful for multi-criteria reporting. If several records satisfy the same criteria, the formula returns the first matching record.

15. Combining XLOOKUP with Other Excel Functions

XLOOKUP can be combined with other functions to build more advanced lookup models.

15.1 XLOOKUP with IFERROR

IFERROR can handle errors that arise from a lookup or from calculations performed around the lookup.

=IFERROR(XLOOKUP(E2,A2:A10,B2:B10),"Check Lookup Data")

For a simple missing-match message, the XLOOKUP if_not_found argument is usually sufficient. IFERROR is more useful when you also want to catch other errors.

15.2 XLOOKUP with SUM

If a lookup returns a numeric value, SUM can be used for further calculations. For example, if E2 contains a product code and C2:C10 contains prices:

=SUM(XLOOKUP(E2,A2:A10,C2:C10,0))

This formula sums the returned price. Because the lookup returns one value, SUM is not essential in this simple example, but it can be useful when working with arrays or more complex calculations.

15.3 XLOOKUP with IF

IF can check a condition before performing a lookup.

=IF(E2="","Enter Product Code",XLOOKUP(E2,A2:A10,C2:C10,"Product Not Found"))

This formula displays a prompt when the lookup cell is blank and searches for the product only when a code is entered.

16. Nested XLOOKUP for Two-Way Searching

A two-way lookup retrieves a value at the intersection of a selected row and a selected column. Nested XLOOKUP formulas can perform this task without requiring separate INDEX and MATCH functions.

Suppose a report has product names in A2:A5 and monthly sales values in B1:E5, with month headings in B1:E1.

Assume the requested product is in G2 and the requested month is in G3.

=XLOOKUP(G2,A2:A5,XLOOKUP(G3,B1:E1,B2:E5),"Product or Month Not Found")

The inner XLOOKUP selects the column corresponding to the chosen month. The outer XLOOKUP then finds the product row and returns the value at the intersection.

Make sure the row labels, column headings, and data ranges are aligned correctly. For example, the month header range B1:E1 must correspond to the data columns B2:E5.

17. XLOOKUP vs. VLOOKUP vs. HLOOKUP

XLOOKUP is generally more flexible for new workbooks. However, VLOOKUP and HLOOKUP remain useful when compatibility with older Excel versions is important.

18. Common XLOOKUP Errors and Troubleshooting

19. Best Practices for Using XLOOKUP

• Use exact matching for employee IDs, product codes, invoice numbers, and other unique identifiers.
• Use the if_not_found argument to provide meaningful messages.
• Keep lookup and return arrays aligned to the same records.
• Use absolute references when copying formulas across multiple cells.
• Check whether the lookup data contains duplicate values.
• Use search_mode -1 when the last occurrence is required and the data order supports that approach.
• Use approximate matching only when the business rule calls for it.
• Sort data correctly before using binary-search modes.
• Clean text data and maintain consistent number formats.
• Use Excel Tables and structured references for datasets that grow regularly.
• Test missing values, duplicate values, and boundary cases.
• Check compatibility before sharing the workbook with users of older Excel versions.

20. Practical Exercise

Task: Build an employee information lookup worksheet using XLOOKUP.

Enter the following sample data into Excel:

Assume the employee IDs are in A2:A5, names in B2:B5, departments in C2:C5, and salaries in D2:D5. Enter an employee ID in F2.

To retrieve the employee name, enter this formula in G2:

=XLOOKUP(F2,A2:A5,B2:B5,"Employee Not Found")

To retrieve the department, enter:

=XLOOKUP(F2,A2:A5,C2:C5,"Department Not Found")

To retrieve the salary, enter:

=XLOOKUP(F2,A2:A5,D2:D5,"Salary Not Found")

Additional Practice Tasks

1. Search for a product code and return its price.
2. Search for a customer ID and return the customer's name and city.
3. Use wildcard matching to find a product by part of its name.
4. Retrieve the last recorded price for a repeated product code.
5. Use approximate matching to determine a commission rate.
6. Create a two-way lookup for monthly sales by employee.
7. Display a custom message for invalid or missing lookup values.
8. Test formulas with duplicate IDs, blank cells, and inconsistent text.

21. Mini Project: Automated Employee and Product Lookup Dashboard

Objective: Create a workbook that allows users to search employee and product information through input cells.

Project Requirements

• Create an employee database containing IDs, names, departments, and salaries.
• Create a product database containing codes, names, prices, and stock quantities.
• Add an input cell for an employee ID and retrieve the corresponding employee details.
• Add a separate input cell for a product code and retrieve product information.
• Use custom messages for missing records.
• Use XLOOKUP with multiple return columns where supported.
• Include a search for the latest recorded price in a transaction log.
• Use appropriate number formats and clear headings.
• Test the dashboard with valid and invalid identifiers.

Expected Outcome

The finished dashboard should allow users to search records quickly, retrieve the correct information, and identify missing records without manually scanning large datasets.

22. Interview Questions and Answers

Q1. What is XLOOKUP in Excel?

Answer: XLOOKUP searches a range or array for a value and returns the corresponding item from another range or array.

Q2. What is the syntax of XLOOKUP?

Answer: =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]).

Q3. What is the default match mode?

Answer: The default is 0, which performs an exact match.

Q4. Can XLOOKUP return values from the left of the lookup column?

Answer: Yes. The lookup array and return array can be specified independently, so the return range can be to the left or right of the lookup range.

Q5. How can you handle a missing lookup value?

Answer: Use the optional if_not_found argument to return a message such as "Not Found" instead of #N/A.

Q6. What is the difference between match_mode and search_mode?

Answer: Match mode controls how values are matched, while search mode controls the direction or algorithm used to search the lookup array.

Q7. Can XLOOKUP return multiple columns?

Answer: Yes. In Excel versions with dynamic array support, a return array containing multiple columns can return multiple results from one formula.

Q8. How can XLOOKUP find the last matching record?

Answer: Set search_mode to -1 to search from the last item to the first.

Q9. What is the difference between XLOOKUP and VLOOKUP?

Answer: XLOOKUP supports leftward and rightward lookups, defaults to exact matching, and does not require a column index number. VLOOKUP searches the first column of its table array and returns a result from a column to its right.

Q10. Which Excel versions support XLOOKUP?

Answer: XLOOKUP is available in Microsoft 365, Excel for the web, and supported newer perpetual versions such as Excel 2021 and Excel 2024. It is not natively available in Excel 2016 or Excel 2019.

23. Quick Revision Checklist

• Understand the six XLOOKUP arguments.
• Use the first three arguments for basic searches.
• Use if_not_found to handle missing matches.
• Use match_mode for exact, approximate, or wildcard matching.
• Use search_mode -1 to search from last to first.
• Return multiple columns when dynamic arrays are supported.
• Use nested XLOOKUP for two-way searches.
• Keep lookup and return ranges correctly aligned.
• Check duplicate records, data types, and compatibility.
• Test all formulas with valid, invalid, and missing values.

24. Conclusion

XLOOKUP is one of the most flexible lookup functions available in modern Excel. It makes it easier to retrieve information from structured datasets, search in either direction, perform approximate matches, locate the last matching record, and return multiple columns from a single formula.

By mastering XLOOKUP, Excel users can build efficient employee databases, product catalogs, financial reports, sales dashboards, and automated search tools. Reliable results depend on well-organized source data, correctly aligned lookup ranges, suitable match settings, and careful testing.

For additional Excel learning, explore the Advanced Excel Course at JustAcademy, visit the JustAcademy official website, or register for a course demo.

25. Reference

For official syntax, match modes, search modes, and additional examples, refer to Microsoft Support: XLOOKUP Function.